﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_COM_USER_MGT_Get_Pw_Expired_Using_Yn')
	BEGIN
		DROP Procedure usp_UPDMS_COM_USER_MGT_Get_Pw_Expired_Using_Yn
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_COM_USER_MGT_Get_Pw_Expired_Using_Yn
**	Desc : 비밀번호 만료기능 사용여부 리턴
**	Test Exec Query : Exec usp_UPDMS_COM_USER_MGT_Get_Pw_Expired_Using_Yn 'smstyle','C5E62038D2AAC98CA74EE83148A8134D42693A20'
**	Called by : Index.aspx.cs
**	Program ID : Index
**	Auth : 송시명
**	Date : 2013-12-09
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**	
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_COM_USER_MGT_Get_Pw_Expired_Using_Yn]
@ls_user_id nvarchar(20),
@ls_pw nvarchar(50)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @pwUpdateDt INT
DECLARE @settingValue INT
DECLARE @rslt CHAR(1)

SET @pwUpdateDt = -1
SET @settingValue = -1
SET @rslt = 'N'

SELECT @settingValue = Setting_Value
  FROM UPDMS_CFG_SETTING_MGT WITH(NOLOCK)
 WHERE Setting_Cd = 'COM_Opt_01'
   AND Use_Yn = 'Y'
 
SELECT @pwUpdateDt = DATEDIFF(DAY, CONVERT(DATETIME, Pw_Update_Dt), GETDATE())
  FROM UPDMS_COM_USER_MGT WITH(NOLOCK)
 WHERE User_Id = @ls_user_id
   AND Pw = @ls_pw
   AND Use_Yn = 'Y'

--설정값이 경과일보다 작으면 변경대상이므로 Y를 리턴
--설정값이 경과일보다 같거나 크면 N을 리턴
--SELECT @settingValue AS Setting_Value, @pwUpdateDt AS Pw_Update_Dt

IF @settingValue < @pwUpdateDt
BEGIN
    SET @rslt = 'Y'
END

--설정을 사용하지 않으면 N을 리턴
IF @settingValue < 0
BEGIN
    SET @rslt = 'N'
END

SELECT @rslt Rslt

GO